﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using School.Entities;

namespace School.Common
{
    public static class Students
    {
        private static SchoolEntities db = new SchoolEntities();

        public static IEnumerable<TE_Student> GetStudentsByGrade(long grade)
        {
            return from x in db.TE_Student where x.Grade_ID == grade orderby x.Gender_Id, x.Ar_FirstName, x.Ar_SecondName, x.Ar_ThirdNmae, x.Ar_FourthName select x;
        }

        public static IEnumerable<TE_Student> GetStudentsByClass(int Class)
        {
            var CurrentYear = Structure.GetCurrentEductionalYear().Select(x => x.Id).First();

            return from x in db.TE_Student where x.TE_Class_Student.Where(y => y.Student_Id == y.Student_Id && y.EductionalYear_Id == CurrentYear).Select(y => y.Class_Id).Contains(Class) == true select x;
        }

        public static IEnumerable<object> Search(long id = 0, string name = null)
        {
            var data = from x in db.TE_Student select new { x.Id, StudentName = x.Ar_FirstName + " " + x.Ar_SecondName + " " + x.Ar_ThirdNmae + " " + x.Ar_FourthName, StudentNumber = x.RegistrationNumber, Certificate = x.ORG_Certificate.Name, Grade = x.ORG_Grade.Name };

            List<object> all = new List<object>();

            if (id > 0)
                data = data.Where(x => x.StudentNumber == id);

            if (!string.IsNullOrEmpty(name) && !string.IsNullOrWhiteSpace(name))
            {
                var start = data.Where(x => x.StudentName.StartsWith(name)).OrderBy(x => x.StudentName);
                var contains = data.Where(x => x.StudentName.Contains(name)).OrderBy(x => x.StudentName);

                all.AddRange(start);
                all.AddRange(contains);
            }
            return all.Distinct();
        }

        public static IEnumerable<object> SearchDetailedFiltered(long campus, long certificate, long stage, long grade, string name)
        {
            var query = from student in db.TE_Student                        
                        join father in db.SD_Father on student.Id equals father.Student_Id into fatherTmp
                        from _father in fatherTmp.DefaultIfEmpty()
                        join fathQualification in db.LU_Qualification on _father.Qualification_Id equals fathQualification.Id into fathqualiTmp
                        from _fathQualification in fathqualiTmp.DefaultIfEmpty()
                        join fathjob in db.LU_Jobs on _father.job_Id equals fathjob.Id into fatherJobTmp
                        from _fathjob in fatherJobTmp.DefaultIfEmpty()
                        join mother in db.SD_Mother on student.Id equals mother.Student_Id into motherTmp
                        from _mother in motherTmp.DefaultIfEmpty()
                        join mothQualification in db.LU_Qualification on _mother.Qualification_Id equals mothQualification.Id into motherqualiTmp
                        from _mothQualification in motherqualiTmp.DefaultIfEmpty()
                        join mothjob in db.LU_Jobs on _mother.job_Id equals mothjob.Id into motherJobTmp
                        from _mothjob in motherJobTmp.DefaultIfEmpty()

                        join relign in db.LU_Religion on student.Religion_Id equals relign.Id into relignTmp
                        from _relign in relignTmp.DefaultIfEmpty()
                        join gend in db.LU_Gender on student.Gender_Id equals gend.Id into gendTmp
                        from _gend in gendTmp.DefaultIfEmpty()
                        select
                            new
                            {
                                Id = student.Id,
                                FullName = student.Ar_FirstName + " " + student.Ar_SecondName + " " + student.Ar_ThirdNmae + " " + student.Ar_FourthName,
                                CampusName = student.ORG_Campus.Name,
                                Stage_Id = student.Stage_Id,
                                Certificate_Id = student.Certificate_Id,
                                Campus_Id = student.Campus_Id,
                                Grade_ID = student.Grade_ID,
                                CertificateName = student.ORG_Certificate.Name,
                                StageName = student.ORG_Stage.Name,
                                GradeName = student.ORG_Grade.Name,
                                Status_Id = student.Status_Id,
                                student.Gender_Id,
                                student.RegistrationNumber,
                                student.FileNumber,
                                student.Gregorian_BirthDate,
                                student.ID_Number,
                                student.Passport_Number,
                                relign = _relign.Name,                                
                                gender = _gend.Kind,
                                father = _father.Ar_FirstName + " " + _father.Ar_SecondName + " " + _father.Ar_ThirdName + " " + _father.Ar_FourthName,
                                fatherSpecialization = _father.Specialization,
                                fatherPointOfGraduation = _father.PointOfGraduation,
                                fatherJob = _fathjob.Name,
                                fatherQualification = _fathQualification.Name,
                                motherSpecialization = _mother.Specialization,
                                motherPointOfGraduation = _mother.PointOfGraduation,
                                motherJob = _mothjob.Name,
                                motherQualification = _mothQualification.Name
                            };

            if (campus > 0)
                query = query.Where(x => x.Campus_Id == campus);

            if (certificate > 0)
                query = query.Where(x => x.Certificate_Id == certificate);

            if (stage > 0)
                query = query.Where(x => x.Stage_Id == stage);

            if (grade > 0)
                query = query.Where(x => x.Grade_ID == grade);

            if (!string.IsNullOrEmpty(name) && !string.IsNullOrWhiteSpace(name))
            {
                var start = query.OrderBy(x => x.FullName).Where(x => x.FullName.StartsWith(name));
                var anywhere = query.OrderBy(x => x.FullName).Where(x => x.FullName.Contains(name));

                query = start.Union(anywhere);
            }

            return query;
        }

        public static IEnumerable<object> SearchFiltered(long campus, long certificate, long stage, long grade, string name)
        {
            var query = from student in db.TE_Student                        
                        select
                            new
                            {
                                Id = student.Id,
                                FullName = student.Ar_FirstName + " " + student.Ar_SecondName + " " + student.Ar_ThirdNmae + " " + student.Ar_FourthName,
                                CampusName = student.ORG_Campus.Name,
                                Stage_Id = student.Stage_Id,
                                Certificate_Id = student.Certificate_Id,
                                Campus_Id = student.Campus_Id,
                                Grade_ID = student.Grade_ID,
                                CertificateName = student.ORG_Certificate.Name,
                                StageName = student.ORG_Stage.Name,
                                GradeName = student.ORG_Grade.Name,
                                Status_Id = student.Status_Id,
                                student.Gender_Id,
                                student.RegistrationNumber,
                                student.FileNumber,
                                student.Gregorian_BirthDate,
                                student.ID_Number,
                                student.Passport_Number,                               
                            };

            if (campus > 0)
                query = query.Where(x => x.Campus_Id == campus);

            if (certificate > 0)
                query = query.Where(x => x.Certificate_Id == certificate);

            if (stage > 0)
                query = query.Where(x => x.Stage_Id == stage);

            if (grade > 0)
                query = query.Where(x => x.Grade_ID == grade);

            if (!string.IsNullOrEmpty(name) && !string.IsNullOrWhiteSpace(name))
            {
                var start = query.OrderBy(x => x.FullName).Where(x => x.FullName.StartsWith(name));
                var anywhere = query.OrderBy(x => x.FullName).Where(x => x.FullName.Contains(name));

                query = start.Union(anywhere);
            }

            return query;
        }
    }
}
